package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.AdwordRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.Adword;
import com.ndp.fb.rdb.model.Dictionary;
import com.ndp.fb.rdb.model.Pager;
import com.ndp.fb.util.SQLUtil;

import java.util.List;

/**
 * Created by Jacky on 2015/1/29.
 */
@Service
public class AdwordRdbServiceImpl extends DaoTemplate implements AdwordRdbService {

    @Override
    public Long saveAdword(final Adword adword) {
        return saveResultId(adword);
    }


    @Override
    public void deleteByIdAndUserId(String userId, Long id) {
        String sql = "delete from adword where user_id= '" + userId + "'  and id= '" + id + "'";
        executeSql(sql);
    }

    @Override
    public Adword findAdwordById(long id) {
        String sql = "SELECT * from adword where id = ?";
        return findOne(sql, new Object[]{id}, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public List<Dictionary> searchLanguageByApp(long fbConnectionObjectId, Long orgId) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`dict_name`, t_dictionary.`dict_value` "
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE adword.`product_id` = ? AND adword.`org_id` = ?";

        return getJdbcTemplate().query(sql, new Object[]{fbConnectionObjectId, orgId}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Dictionary> searchLanguageByAppEN(long fbConnectionObjectId, Long orgId) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`en_dict_name` AS dict_name, t_dictionary.`dict_value` "
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE  adword.`product_id` = ? AND adword.`org_id` = ?";
        return getJdbcTemplate().query(sql, new Object[]{fbConnectionObjectId, orgId}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Dictionary> searchLanguageByOrg(Long orgId) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`dict_name`, t_dictionary.`dict_value`"
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE adword.`org_id` = ? ";
        return getJdbcTemplate().query(sql, new Object[]{orgId}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Dictionary> searchLanguageByOrgEN(Long orgId) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`en_dict_name` AS dict_name, t_dictionary.`dict_value`"
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE adword.`org_id` = ? ";
        return getJdbcTemplate().query(sql, new Object[]{orgId}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Adword> findByOrgIdAndDictId(Long orgId, Long languageId) {
        String sql = "SELECT * FROM adword WHERE adword.`org_id` = " + orgId;
        if (null != languageId && -1 != languageId) {
            sql += " AND adword.`dict_id` = " + languageId;
        }

        sql += " ORDER BY create_time DESC";

        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(Adword.class));
    }

//    @Override
//    public List<Adword> findByFbConnectionObjectIdAndOrgIdAndDictId(Long fbConnectionObjectId, Long orgId, Long languageId) {
//        String sql = "SELECT adword.* FROM connection_object_adwords"
//         + " INNER JOIN adword ON connection_object_adwords.`adword_id` = adword.`id`"
//         + " WHERE connection_object_adwords.`fb_connection_object_id` =  " + fbConnectionObjectId
//         + " AND adword.`org_id` = " + orgId;
//        if (null != languageId && -1 != languageId){
//            sql += " AND adword.`dict_id` =" + languageId;
//        }
//
//        sql += " ORDER BY create_time DESC";
//
//        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(Adword.class));
//    }

    @Override
    public Pager<?> findPagerByOrgIdAndDictId(Long orgId, Long languageId, int firstResult, int sizeNo) {
        String sql = "SELECT * FROM adword WHERE adword.`org_id` = " + orgId;
        if (null != languageId && -1 != languageId) {
            sql += " AND adword.`dict_id` = " + languageId;
        }

        sql += " ORDER BY create_time DESC";

        return findPager(sql, null, firstResult, sizeNo, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public Pager<?> findPagerByFbConnectionObjectIdAndOrgIdAndDictId(Long productId, Long orgId, Long languageId, int firstResult, int sizeNo) {
        String sql = "SELECT adword.* FROM adword"
                + " WHERE adword.`product_id` =  " + productId
                + " AND adword.`org_id` = " + orgId;
        if (null != languageId && -1 != languageId) {
            sql += " AND adword.`dict_id` =" + languageId;
        }

        sql += " ORDER BY create_time DESC";

        return findPager(sql, null, firstResult, sizeNo, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public Adword findAdwordByOrgIdAndDictIdAndDescriptionAndWord(Long orgId, Long languageId, String description, String word) {
        String sql = "SELECT * FROM adword WHERE word = ? AND org_id = ? ";
        if (null != languageId || (languageId >0)) {
            sql += "AND dict_id = " + languageId;
        }

        if (null == description) {
            sql += " AND description is null ";
        } else {
            sql += " AND description = '" + description + "'";
        }
        return findOne(sql, new Object[]{word, orgId}, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public List<Adword> findByProductIdAndOrgIdAndLanaguage(Long productId, Long orgId, Long languageId) {
        if (null == languageId || -1 == languageId){
            String sql = "SELECT * from adword a where a.org_id = ? and a.product_id = ? ORDER BY a.create_time DESC";
            return getJdbcTemplate().query(sql, new Object[]{orgId, productId}, DefaultRowMapper.autoRowMapper(Adword.class));
        } else {
            String sql = "SELECT * from adword a where a.org_id = ? and a.product_id = ? and a.dict_id = ? ORDER BY a.create_time DESC";
            return getJdbcTemplate().query(sql, new Object[]{orgId, productId, languageId}, DefaultRowMapper.autoRowMapper(Adword.class));
        }
    }

    @Override
    public Pager<Adword> findPagerByProductIdAndOrgIdAndDictId(Long productId, Long orgId, Long languageId, int firstResult, int sizeNo) {
        Object[] params;
        String sql = "SELECT * FROM adword a WHERE a.org_id = ? AND a.product_id = ? ";
        if (null != languageId && -1 != languageId){
            sql += " AND a.dict_id = ? ORDER BY a.create_time DESC";
            params = new Object[]{orgId, productId, languageId};
        } else {
            sql += " ORDER BY a.create_time DESC";
            params = new Object[]{orgId, productId};
        }
        return findPager(sql, params, firstResult, sizeNo, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public Adword findByProductIdAndOrgIdAndLanaguageAndWord(Long productId, Long orgId, Long languageId, String word) {
        String sql = "SELECT * from adword a where a.org_id = ? and a.product_id = ? and a.dict_id = ? and word = ?";
        return findOne(sql, new Object[]{orgId, productId, languageId, word}, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public Adword findRandomForRobot(Long orgId,Long productId) {
    	String sql = "SELECT * from adword a where a.org_id = ? and a.product_id = ? limit 0,1";
        return findOne(sql, new Object[]{orgId, productId}, DefaultRowMapper.autoRowMapper(Adword.class));

    }

    @Override
    public List<Adword> findAdwordByProductId(Long productId) {
        String sql = "SELECT * FROM adword WHERE product_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{productId}, DefaultRowMapper.autoRowMapper(Adword.class));
    }

    @Override
    public List<Dictionary> searchLanguageByOrg(Long orgId, String dicType) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`dict_name`, t_dictionary.`dict_value`"
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE adword.`org_id` = ? and dict_type = ? ";
        return getJdbcTemplate().query(sql, new Object[]{orgId,dicType}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Dictionary> searchLanguageByApp(Long fbConnectionObjectId, Long orgId, String dicType) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`dict_name`, t_dictionary.`dict_value` "
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE adword.`product_id` = ? AND adword.`org_id` = ? and dict_type = ?";

        return getJdbcTemplate().query(sql, new Object[]{fbConnectionObjectId, orgId,dicType}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Dictionary> searchLanguageByOrgEN(Long orgId, String dicType) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`en_dict_name` AS dict_name, t_dictionary.`dict_value`"
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE adword.`org_id` = ? and dict_type = ? ";
        return getJdbcTemplate().query(sql, new Object[]{orgId,dicType}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }

    @Override
    public List<Dictionary> searchLanguageByAppEN(Long fbConnectionObjectId, Long orgId, String dicType) {
        String sql = "SELECT DISTINCT t_dictionary.id,t_dictionary.`dict_type`, t_dictionary.`en_dict_name` AS dict_name, t_dictionary.`dict_value` "
                + "FROM adword "
                + "INNER JOIN `t_dictionary` ON adword.`dict_id` = t_dictionary.`id` "
                + "WHERE  adword.`product_id` = ? AND adword.`org_id` = ? and dict_type = ?";
        return getJdbcTemplate().query(sql, new Object[]{fbConnectionObjectId, orgId, dicType}, DefaultRowMapper.autoRowMapper(Dictionary.class));
    }


	@Override
	public Adword findRandomForRobot(Long orgId, Long productId, List<String> language) {
		String sql = "SELECT adword.id,adword.user_id,adword.word,adword.description,adword.create_time,adword.org_id,adword.dict_id,adword.product_id "
				+ "FROM t_dictionary INNER JOIN adword ON adword.dict_id = t_dictionary.id "
				+ "WHERE t_dictionary.dict_type = 'adword' AND adword.product_id = "+productId+" and adword.`org_id` = "+orgId+" AND t_dictionary.en_dict_name IN ("+SQLUtil.appendPlaceholder(language.size())+") ";
        return findOne(sql, language.toArray(), DefaultRowMapper.autoRowMapper(Adword.class));
	}
}
